package organizer.derby;

import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import organizer.Duplicate;
import organizer.FileUtils;
import organizer.ICancelable;
import organizer.IProgressMonitor;
import organizer.jdbc.JdbcPreparedCallback;
import organizer.jdbc.JdbcPreparedEachCallback;
import organizer.jdbc.JdbcPreparedResultsCallback;
import organizer.jdbc.JdbcUtil;

/**
 *
 * @author dawong
 */
public class DerbyOrganizerDao {

    private DataSource dataSource;

    private static Logger log = Logger.getLogger(DerbyOrganizerDao.class);

    public DerbyOrganizerDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private static final String SQL_INSERT_FILE =
            "insert into filemeta (path, filesize, modified_date, scanned) values (?,?,?, 1)";
    private static final String SQL_SELECT_FILE =
            "select filesize, modified_date from filemeta where path = ?";
    private static final String SQL_UPDATE_FILE = 
            "update filemeta set filesize=?, modified_date=?, small_checksum=NULL, "
            + "checksum=NULL, scanned=1 where path=?";
    private static final String SQL_UPDATE_SCANNED = 
            "update filemeta set scanned=1 where path=?";
    private FileInfo tmpFileInfo = new FileInfo();

    public void addFile(File baseDir, final File f) {
        try {
            final String path = f.getCanonicalPath();
            final long fileLength = f.length();
            final long modified = f.lastModified();
            JdbcUtil jdbc = JdbcUtil.instance(dataSource);
            FileInfo info = jdbc.unique(SQL_SELECT_FILE, new JdbcPreparedResultsCallback<FileInfo>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                    ps.setString(1, path);
                }

                public FileInfo getRow(ResultSet rs) throws SQLException {
                    long filesize = rs.getLong(1);
                    long modified = rs.getLong(2);
                    tmpFileInfo.load(path, filesize, modified);
                    return tmpFileInfo;
                }
            });
            if (info == null) {
                jdbc.exec(SQL_INSERT_FILE, new JdbcPreparedCallback() {

                    public void setParams(PreparedStatement ps) throws SQLException {
                        ps.setString(1, path);
                        ps.setLong(2, fileLength);
                        ps.setLong(3, modified);
                    }
                });
            } else {
                if (fileLength != info.length || modified != info.modifiedDate) {
                    // file has changed!
                    jdbc.exec(SQL_UPDATE_FILE, new JdbcPreparedCallback() {

                        public void setParams(PreparedStatement ps) throws SQLException {
                            ps.setLong(1, fileLength);
                            ps.setLong(2, modified);
                            ps.setString(3, path);
                        }
                    });
                } else {
                    // just marked as scanned
                    jdbc.exec(SQL_UPDATE_SCANNED, new JdbcPreparedCallback() {

                        public void setParams(PreparedStatement ps) throws SQLException {
                            ps.setString(1, path);
                        }
                    });
                }
            }
        } catch (IOException ex) {
            log.error(null, ex);
        } catch (SQLException ex) {
            log.error(null, ex);
        }
    }

    private static final String SQL_SELECT_SIZE_DUPS =
            "select f1.path, f1.small_checksum, "
            + "f2.path, f2.small_checksum, f1.filesize "
            + "from filemeta f1, filemeta f2 "
            + "where f1.path != f2.path "
            + "and f1.filesize = f2.filesize "
            + "and f1.scanned = 1";

    private static final String SQL_SELECT_SMALL_DUPS =
            "select f1.path, f1.checksum, "
            + "f2.path, f2.checksum, f1.filesize "
            + "from filemeta f1, filemeta f2 "
            + "where f1.path != f2.path and f1.small_checksum = f2.small_checksum "
            + "and f1.filesize = f2.filesize and f1.small_checksum is not null "
            + "and f1.scanned=1";

    public void calcDuplicates(final IProgressMonitor progress) {
        try {
            log.info("Looking for sample dups");
            JdbcUtil jdbc = JdbcUtil.instance(dataSource);
            List<DupResult> sizeDupResults = jdbc
                    .list(SQL_SELECT_SIZE_DUPS, new JdbcPreparedResultsCallback<DupResult>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                }

                public DupResult getRow(ResultSet rs) throws SQLException {
                    int i = 1;
                    String f1 = rs.getString(i++);
                    String small1 = rs.getString(i++);
                    String f2 = rs.getString(i++);
                    String small2 = rs.getString(i++);
                    long size = rs.getLong(i++);
                    DupResult result = new DupResult(f1, small1, f2, small2, size);
                    return result;
                }
            });
            if (sizeDupResults.size() > 0) {
                Set<DupResult> dupSet = new HashSet(sizeDupResults);
                if (progress != null)
                    progress.start(dupSet.size());
                for (DupResult dup : dupSet) {
                    if (progress != null && progress.isCanceled())
                        break;
                    String hash1 = dup.getHash1();
                    String hash2 = dup.getHash2();
                    if (hash1 == null) {
                        File f1 = new File(dup.getPath1());
                        hash1 = getSampleHash(f1);
                        if (hash1 == null) {
                            log.trace("sampling " + f1);
                            hash1 = FileUtils.sampleHash(f1, 512);
                            updateSampleHash(f1, hash1);
                        }
                    }
                    if (hash2 == null) {
                        File f2 = new File(dup.getPath2());
                        hash2 = getSampleHash(f2);
                        if (hash2 == null) {
                            log.trace("sampling " + f2);
                            hash2 = FileUtils.sampleHash(f2, 512);
                            updateSampleHash(f2, hash2);
                        }
                    }
                    if (progress != null)
                        progress.increment(1);
                }
                if (progress != null)
                    progress.stop();
            }
            log.info("Looking for true dups");
            List<DupResult> smallDupResults = jdbc
                    .list(SQL_SELECT_SMALL_DUPS, new JdbcPreparedResultsCallback<DupResult>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                }

                public DupResult getRow(ResultSet rs) throws SQLException {
                    int i = 1;
                    String f1 = rs.getString(i++);
                    String hash1 = rs.getString(i++);
                    String f2 = rs.getString(i++);
                    String hash2 = rs.getString(i++);
                    long size = rs.getLong(i++);
                    if (f1.compareTo(f2) > 0) {
                        String tmp = f1;
                        f1 = f2;
                        f2 = tmp;
                        tmp =  hash1;
                        hash1 = hash2;
                        hash2 = tmp;
                    }
                    DupResult result = new DupResult(f1, hash1, f2, hash2, size);
                    return result;
                }
            });
            if (smallDupResults.size() > 0) {
                Set<DupResult> dupSet = new HashSet(smallDupResults);
                if (progress != null)
                    progress.start(dupSet.size());
                for (DupResult dup : dupSet) {
                    if (progress != null && progress.isCanceled())
                        break;
                    String hash1 = dup.getHash1();
                    String hash2 = dup.getHash2();
                    if (hash1 == null) {
                        File f1 = new File(dup.getPath1());
                        hash1 = getHash(f1);
                        if (hash1 == null) {
                            log.trace("hashing " + f1);
                            hash1 = FileUtils.hash(f1);
                            updateHash(f1, hash1);
                        }
                    }
                    if (hash2 == null) {
                        File f2 = new File(dup.getPath2());
                        hash2 = getHash(f2);
                        if (hash2 == null) {
                            log.trace("hashing " + f2);
                            hash2 = FileUtils.hash(f2);
                            updateHash(f2, hash2);
                        }
                    }
                    if (progress != null)
                        progress.increment(1);
                }
                if (progress != null)
                    progress.stop();
            }
        } catch (SQLException e) {
            log.error(null, e);
        }
    }

    private static final String SQL_SELECT_DUPS =
            "select f1.path, f1.checksum, "
            + "f2.path, f2.checksum "
            + "from filemeta f1, filemeta f2 "
            + "where f1.path != f2.path and f1.checksum = f2.checksum "
            + "and f1.checksum is not null "
            + "and f1.scanned=1 "
            + "order by f1.checksum";

    public List<Duplicate> getDuplicates() {
        List<Duplicate> ret = Collections.emptyList();
        JdbcUtil jdbc = JdbcUtil.instance(dataSource);
        try {
            GroupDupsCallback callback = new GroupDupsCallback();
            jdbc.each(SQL_SELECT_DUPS, callback, null);
            ret = callback.getDups();
            Duplicate lastDup = callback.getLastDuplicate();
            if (lastDup != null) {
                ret.add(lastDup);
            }
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return ret;
    }

    private static class GroupDupsCallback implements JdbcPreparedEachCallback {

        private List<Duplicate> dups = new ArrayList<Duplicate>();
        private Duplicate lastDup = null;
        private String lastChecksum = null;

        public void setParams(PreparedStatement ps) throws SQLException {
        }

        public void row(ResultSet rs) throws SQLException {
            int i = 1;
            String path1 = rs.getString(i++);
            String checksum1 = rs.getString(i++);
            String path2 = rs.getString(i++);
            String checksum2 = rs.getString(i++);
            log.trace("grouping dup: " + path1 + " ---- " + path2);
            if (lastChecksum == null || !checksum1.equals(lastChecksum)) {
                if (lastDup != null)
                    dups.add(lastDup);
                lastDup = new Duplicate(checksum1);
                lastChecksum = checksum1;
            }
            lastDup.add(path1);
            lastDup.add(path2);
        }

        public List<Duplicate> getDups() {
            return dups;
        }

        public Duplicate getLastDuplicate() {
            return lastDup;
        }

    }

    private static final String SQL_CLEAR_SCANNED =
            "update filemeta set scanned=0";

    /**
     * Remove all the invalid files in the database
     */
    public void purge(final ICancelable cancel) {
        try {
            JdbcUtil jdbc = JdbcUtil.instance(dataSource);
            jdbc.exec(SQL_CLEAR_SCANNED, null);
        } catch (SQLException e) {
            log.error(null, e);
        }
    }

    private static final String SQL_GET_HASH_BY_FILE =
            "select checksum from filemeta where path=?";

    private String getHash(final File file) {
        String ret = "";
        try {
            List<String> results = JdbcUtil.instance(dataSource)
                    .list(SQL_GET_HASH_BY_FILE, new JdbcPreparedResultsCallback<String>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                    String path = "";
                    try {
                        path = file.getCanonicalPath();
                    } catch (IOException ex) {
                        log.error(null, ex);
                    }
                    ps.setString(1, path);
                }

                public String getRow(ResultSet rs) throws SQLException {
                    return rs.getString(1);
                }
            });
            if (results.size() > 0) {
                ret = results.get(0);
            }
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return ret;
    }

    private static final String SQL_GET_SAMPLE_HASH_BY_FILE =
            "select small_checksum from filemeta where path=?";

    private String getSampleHash(final File file) {
        String ret = "";
        try {
            List<String> results = JdbcUtil.instance(dataSource)
                    .list(SQL_GET_SAMPLE_HASH_BY_FILE, new JdbcPreparedResultsCallback<String>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                    String path = "";
                    try {
                        path = file.getCanonicalPath();
                    } catch (IOException ex) {
                        log.error(null, ex);
                    }
                    ps.setString(1, path);
                }

                public String getRow(ResultSet rs) throws SQLException {
                    return rs.getString(1);
                }
            });
            if (results.size() > 0) {
                ret = results.get(0);
            }
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return ret;
    }

    private static final String SQL_UPDATE_HASH_BY_FILE =
            "update filemeta set checksum=? where path=?";

    private void updateHash(final File file, final String hash) {
        try {
            JdbcUtil.instance(dataSource).exec(SQL_UPDATE_HASH_BY_FILE,
                    new JdbcPreparedCallback() {

                public void setParams(PreparedStatement ps) throws SQLException {
                    String path = "";
                    try {
                        path = file.getCanonicalPath();
                    } catch (IOException ex) {
                        log.error(null, ex);
                    }
                    ps.setString(1, hash);
                    ps.setString(2, path);
                }
            });
        } catch (SQLException ex) {
            log.error(null, ex);
        }
    }

    private static final String SQL_UPDATE_SAMPLE_HASH_BY_FILE =
            "update filemeta set small_checksum=? where path=?";

    private void updateSampleHash(final File file, final String hash) {
        try {
            JdbcUtil.instance(dataSource).exec(SQL_UPDATE_SAMPLE_HASH_BY_FILE,
                    new JdbcPreparedCallback() {

                public void setParams(PreparedStatement ps) throws SQLException {
                    String path = "";
                    try {
                        path = file.getCanonicalPath();
                    } catch (IOException ex) {
                        log.error(null, ex);
                    }
                    ps.setString(1, hash);
                    ps.setString(2, path);
                }
            });
        } catch (SQLException ex) {
            log.error(null, ex);
        }
    }
    
    private static final String SQL_COUNT_SCANNED =
            "select count(*) from filemeta where scanned=1";
    
    public long getScannedFilesCount() {
        try {
            return JdbcUtil.instance(dataSource).unique(SQL_COUNT_SCANNED,
                    new JdbcPreparedResultsCallback<Long>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                }

                public Long getRow(ResultSet rs) throws SQLException {
                    return rs.getLong(1);
                }
            });
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return 0;
    }
    
    private static final String SQL_COUNT_ALL =
            "select count(*) from filemeta";
    
    public long getFilesInRepositoryCount() {
        try {
            return JdbcUtil.instance(dataSource).unique(SQL_COUNT_ALL,
                    new JdbcPreparedResultsCallback<Long>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                }

                public Long getRow(ResultSet rs) throws SQLException {
                    return rs.getLong(1);
                }
            });
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return 0;
    }
    
    private static final String SQL_COUNT_HASHED_SCANS =
            "select count(*) from filemeta where small_checksum is not null "
            + "and scanned=1";
    
    public long getHashedFileCount() {
        try {
            return JdbcUtil.instance(dataSource).unique(SQL_COUNT_HASHED_SCANS,
                    new JdbcPreparedResultsCallback<Long>() {

                public void setParams(PreparedStatement ps) throws SQLException {
                }

                public Long getRow(ResultSet rs) throws SQLException {
                    return rs.getLong(1);
                }
            });
        } catch (SQLException ex) {
            log.error(null, ex);
        }
        return 0;
    }
    
    private static class FileInfo {
        private String path;
        private long length;
        private long modifiedDate;

        public FileInfo() {
        }

        public FileInfo(String path, long size, long modifiedDate) {
            this.path = path;
            this.length = size;
            this.modifiedDate = modifiedDate;
        }

        public String getPath() {
            return path;
        }

        public long getLength() {
            return length;
        }

        public long getModifiedDate() {
            return modifiedDate;
        }
        
        public void load(String path, long size, long modifiedDate) {
            this.path = path;
            this.length = size;
            this.modifiedDate = modifiedDate;
        }
        
    }

    private static class DupResult {
        private String path1;
        private String hash1;
        private String path2;
        private String hash2;
        private long size;

        public DupResult(String path1, String hash1, String path2, String hash2, long size) {
            this.path1 = path1;
            this.hash1 = hash1;
            this.path2 = path2;
            this.hash2 = hash2;
            this.size = size;
        }

        public String getHash1() {
            return hash1;
        }

        public String getHash2() {
            return hash2;
        }

        public String getPath1() {
            return path1;
        }

        public String getPath2() {
            return path2;
        }

        public long getSize() {
            return size;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj == null) {
                return false;
            }
            if (getClass() != obj.getClass()) {
                return false;
            }
            final DupResult other = (DupResult) obj;
            if ((this.path1 == null) ? (other.path1 != null) : !this.path1.equals(other.path1)) {
                return false;
            }
            if ((this.hash1 == null) ? (other.hash1 != null) : !this.hash1.equals(other.hash1)) {
                return false;
            }
            if ((this.path2 == null) ? (other.path2 != null) : !this.path2.equals(other.path2)) {
                return false;
            }
            if ((this.hash2 == null) ? (other.hash2 != null) : !this.hash2.equals(other.hash2)) {
                return false;
            }
            if (this.size != other.size) {
                return false;
            }
            return true;
        }

        @Override
        public int hashCode() {
            int hash = 7;
            hash = 59 * hash + (this.path1 != null ? this.path1.hashCode() : 0);
            hash = 59 * hash + (this.hash1 != null ? this.hash1.hashCode() : 0);
            hash = 59 * hash + (this.path2 != null ? this.path2.hashCode() : 0);
            hash = 59 * hash + (this.hash2 != null ? this.hash2.hashCode() : 0);
            hash = 59 * hash + (int) (this.size ^ (this.size >>> 32));
            return hash;
        }

        @Override
        public String toString() {
            return "DupResult{" +
                    "path1=" + path1 +
                    ",hash1=" + hash1 +
                    ",path2=" + path2 +
                    ",hash2=" + hash2 +
                    ",size=" + size +
                    '}';
        }

    }

}
